<?php
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 86400);
define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

if (file_exists(dirname(__FILE__) . '/defines.php')) {
	include_once dirname(__FILE__) . '/defines.php';
}

if (!defined('_JDEFINES')) {
	define('JPATH_BASE', dirname(__FILE__));
	require_once JPATH_BASE.'/includes/defines.php';
}

require_once JPATH_BASE.'/includes/framework.php';

// Mark afterLoad in the profiler.
JDEBUG ? $_PROFILER->mark('afterLoad') : null;

// Instantiate the application.
$app = JFactory::getApplication('site');
// Initialise the application.
$app->initialise();

require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'reportProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'pointProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'PHPExcel.php';

$objPHPExcel = new PHPExcel();
// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->mergeCells('A1:P1');
$activeSheet->setCellValue('A1', 'Transaction Report');
$activeSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle('A1')->getFont()->setSize(24);
$activeSheet->getStyle('A1')->getFont()->setBold(true);

/************** FILL HEADER **************/
$index = 6;
$activeSheet->setCellValue('A'.$index, 'Name');
$activeSheet->setCellValue('B'.$index, 'Receipt (Date)');
$activeSheet->setCellValue('C'.$index, 'Receipt (Time)');
$activeSheet->setCellValue('D'.$index, 'Open Shift (Date)');
$activeSheet->setCellValue('E'.$index, 'Outlet Name');
$activeSheet->setCellValue('F'.$index, 'Member ID');
$activeSheet->setCellValue('G'.$index, 'Transaction');

$activeSheet->setCellValue('H'.$index, 'Cover');
$activeSheet->setCellValue('I'.$index, 'Food');
$activeSheet->setCellValue('J'.$index, 'Beverage');
$activeSheet->setCellValue('K'.$index, 'Other');
$activeSheet->setCellValue('L'.$index, 'Discount');
$activeSheet->setCellValue('M'.$index, 'Netsale');
$activeSheet->setCellValue('N'.$index, 'Service Charge');
$activeSheet->setCellValue('O'.$index, 'Tax');
$activeSheet->setCellValue('P'.$index, 'Actual Paid ');
$activeSheet->setCellValue('Q'.$index, 'Points');

$fill = $activeSheet->getStyle('A6:Q6')->getFill();
$fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$fill->getStartColor()->setARGB('cc99ff');
$style = $activeSheet->getStyle('A6:Q6');
$style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$cols = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L','M','N','O','p','Q');
foreach($cols as $item)
{
    if($item == 'A')
    {
        setBackgroundColor($activeSheet, $item, 6, 35);
    }
	if($item == 'E' || $item == 'Q')
    {
        setBackgroundColor($activeSheet, $item, 6, 30);
    }
    else if($item == 'F' || $item == 'G')
    {
        setBackgroundColor($activeSheet, $item, 6, 15);
    }
    else
    {
        setBackgroundColor($activeSheet, $item, 20);   
    }
}
/******** Query Data ********/  
$db = JFactory::getDbo();
$conditions = array();
$conditions['dateFrom'] = strval($_GET['df']);
$conditions['dateTo'] = strval($_GET['dt']);
$conditions['outlet'] = strval($_GET['o']);
//echo '<pre>';print_r($conditions);die;

$data = reportProvider::transactionreport($conditions, $db, intval($_GET['l']));

//echo '<pre>';print_r($data);die;

if(!empty($data))
{
    $activeSheet->setCellValue('A3', 'Business Date From: ');
	$activeSheet->setCellValue('A4', 'Business Date To: ');
	$activeSheet->setCellValue('B3', $conditions['dateFrom']);
	$activeSheet->setCellValue('B4', $conditions['dateTo']);
	
	$activeSheet->setCellValue('D3', 'Total Records: ');
	$activeSheet->setCellValue('E3', count($data['items']));	
	$activeSheet->getStyle('E3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
	$objPHPExcel->getActiveSheet()->getStyle('E3')->getNumberFormat()->setFormatCode("#,##0");
    
	
	/*********** BIND TO EXCEL ***********/
    $index = 8;
    $data = $data['items'];
    $size = count($data);
	
	$cover = 0;$food = 0;$beverage = 0;$other = 0;$discount = 0;$netsale = 0; $sc = 0;$tax = 0; $ap = 0;
    for($i=0; $i<$size; $i++)
    {
		$cover += $data[$i]->pax;$point1 += $data[$i]->netsale;
		$food += $data[$i]->food; $beverage += $data[$i]->beverage;$other += $data[$i]->other;
		$discount += $data[$i]->discount; $netsale += $data[$i]->netsale;$sc += $data[$i]->sc;
		$vat += $data[$i]->vat; $total += $data[$i]->total;
		if($i == ($size - 1)){
			$fill = $activeSheet->getStyle('A7:R7')->getFill();
			$fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
			$fill->getStartColor()->setARGB('f7f7f7');
			$activeSheet->setCellValue('H7',$cover);
			$activeSheet->setCellValue('I7',$food);
			$activeSheet->getStyle('H7:R7'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			$objPHPExcel->getActiveSheet()->getStyle('H7:R7'.$index)->getNumberFormat()->setFormatCode("#,##0");
			
			$activeSheet->setCellValue('J7',$beverage);
			$activeSheet->setCellValue('K7',$other);	
			$activeSheet->setCellValue('L7',$discount);
			$activeSheet->setCellValue('M7',$netsale);
			$activeSheet->setCellValue('N7',$sc);
			$activeSheet->setCellValue('O7',$vat);
			$activeSheet->setCellValue('P7',$total);
			$activeSheet->setCellValue('Q7',"=SUM(Q8:Q".($size+8).")");
		}
		
		
		
		$activeSheet->setCellValue('A'.$index, $data[$i]->l_name ." ".$data[$i]->f_name); 
        $activeSheet->setCellValue('B'.$index, date("d/m/Y",strtotime($data[$i]->date_receipt)));    
        $activeSheet->getStyle('B'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        
        $activeSheet->setCellValue('C'.$index, date("H:i:s",strtotime($data[$i]->date_receipt)));                
        
		$activeSheet->setCellValue('D'.$index, strtotime($data[$i]->endtime) ? date("d/m/Y",strtotime($data[$i]->endtime))  : '' );
		
		$activeSheet->setCellValue('E'.$index, $data[$i]->outletName);                
        $activeSheet->getStyle('E'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        
                     
        $activeSheet->setCellValue('F'.$index,$data[$i]->vip_number);                
        $activeSheet->getStyle('F'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        
        
		$activeSheet->setCellValue('G'.$index,$data[$i]->transaction_id);                
        $activeSheet->getCell('G'.$index)->getHyperlink($data[$i]->transaction_id)->setUrl('http://wmcvip.vn/administrator/index.php?option=com_wrd&controller=profile&transaction_id='.$data[$i]->transaction_id.'&outlet='.$data[$i]->outlet.'&layout=detailtransaction');
		
		$styleArray = array( 'font' => array('underline' => PHPExcel_Style_Font::UNDERLINE_SINGLE));
		$activeSheet->getStyle('G'.$index)->getFont()->setColor( new PHPExcel_Style_Color( '091abb' ) );
		$objPHPExcel->getActiveSheet()->getStyle('G'.$index)->applyFromArray($styleArray);
		
		$activeSheet->getStyle('G'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		
		$activeSheet->setCellValue('H'.$index, $data[$i]->pax);
        $activeSheet->getStyle('H'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyle('H'.$index)->getNumberFormat()->setFormatCode("#,##0");
		
		$activeSheet->setCellValue('I'.$index, $data[$i]->food);
		$activeSheet->getStyle("H$index:R$index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyle("H$index:R$index")->getNumberFormat()->setFormatCode("#,##0");
                 
        $activeSheet->setCellValue('J'.$index,$data[$i]->beverage);
		$activeSheet->setCellValue('K'.$index,$data[$i]->other);
		$activeSheet->setCellValue('L'.$index,$data[$i]->discount);
		$activeSheet->setCellValue('M'.$index,$data[$i]->netsale);
		$activeSheet->setCellValue('N'.$index,$data[$i]->sc);        
		$activeSheet->setCellValue('O'.$index,$data[$i]->vat);
		$activeSheet->setCellValue('P'.$index,$data[$i]->total);		
		$activeSheet->setCellValue('Q'.$index,"=INT(M$index/50000)*100");
       
		$index++;

    }
    unset($data);
}

$fileName = 'transactionreport_'.date("d_m_Y_H_i_s");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(JPATH_BASE.DS.'tmp'.DS.$fileName.'.xlsx');

$fullPath = JPATH_ROOT.DS.'tmp'.DS.$fileName.'.xlsx';
$fsize = filesize($fullPath);

header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="'.$fileName.'.xlsx"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . $fsize);
ob_clean();
flush();
readfile( $fullPath );
exit;          

function setBackgroundColor(&$activeSheet, $colName, $colIndex, $width=15)
{
    $activeSheet->getColumnDimension($colName)->setWidth($width);    
    
    $style = $activeSheet->getStyle($colName.$colIndex);
    $style->getFont()->setSize(12);
    //$style->getFont()->setBold(true);
    
}
